package com.iplatform.file.function.meta.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 读取Excel内容
 *
 * @param <T>
 */
public abstract class AbstractPoiExcelReader<T> {
	
	private static final Logger logger = LoggerFactory.getLogger(AbstractPoiExcelReader.class);
	
	
	private static final String XLS = "xls";
	private static final String XLSX = "xlsx";
	
	/**
	 * 根据文件后缀名类型获取对应的工作簿对象
	 *
	 * @param inputStream 读取文件的输入流
	 * @return 包含文件数据的工作簿对象
	 * @throws IOException
	 */
	public Workbook getWorkbook(InputStream inputStream, String fileName) throws IOException {
		Workbook workbook = null;
		if (fileName.endsWith(XLS) || fileName.endsWith(XLS.toUpperCase())) {
			workbook = new HSSFWorkbook(inputStream);
		} else if (fileName.endsWith(XLSX) || fileName.endsWith(XLSX.toUpperCase())) {
			workbook = new XSSFWorkbook(inputStream);
		}
		return workbook;
	}
	
	/**
	 * 读取Excel文件内容
	 *
	 * @param fileName 要读取的Excel文件所在路径
	 * @return 读取结果列表，读取失败时返回null
	 */
	public List<String[][]> readExcel(String fileName) {
		
		Workbook workbook = null;
		
		FileInputStream inputStream = null;
		
		try {
			// 获取Excel后缀名
			String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
			// 获取Excel文件
			File excelFile = new File(fileName);
			if (!excelFile.exists()) {
				logger.warn("指定的Excel文件不存在！");
				return null;
			}
			
			// 获取Excel工作簿
			inputStream = new FileInputStream(excelFile);
			workbook = getWorkbook(inputStream, fileType);
			// 读取excel中的数据
			List<String[][]> resultDataList = parseExcel(workbook);
			return resultDataList;
		} catch (Exception e) {
			logger.warn("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
			return null;
		} finally {
			try {
				
				if (null != inputStream) {
					inputStream.close();
				}
			} catch (Exception e) {
				logger.warn("关闭数据流出错！错误信息：" + e.getMessage());
			}
		}
	}
	
	/**
	 * 解析Excel数据
	 *
	 * @param workbook Excel工作簿对象
	 * @return 解析结果
	 */
	public List<String[][]> parseExcel(Workbook workbook) {
		List<String[][]> resultDataList = new ArrayList<>();
		// 解析sheet
		for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
			Sheet sheet = workbook.getSheetAt(sheetNum);
			// 校验sheet是否合法
			if (sheet == null) {
				continue;
			}
			
			// 获取第一行数据
			//获得最大行数
			int rowEnd = sheet.getLastRowNum();
			int maxCellNum = 0;
			//获取最大列
			for (int rowNum = 0; rowNum < rowEnd; rowNum++) {
				Row row = sheet.getRow(rowNum);
				if (row == null) {
					continue;
				}
				int cellNum = 0;
				try {
					cellNum = row.getLastCellNum();
				} catch (Exception e) {
					logger.error("获取列数失败");
				}
				if (cellNum > maxCellNum) {
					maxCellNum = cellNum;
				}
			}
			String[][] arr = new String[rowEnd + 1][maxCellNum];
			
			for (int rowNum = 0; rowNum <= rowEnd; rowNum++) {
				Row row = sheet.getRow(rowNum);
				if (null == row) {
					continue;
				}
				arr = convertRowToData(row, arr, rowNum);
			}
			resultDataList.add(arr);
		}
		return resultDataList;
	}
	
	/**
	 * 解析Excel数据
	 *
	 * @param workbook Excel工作簿对象
	 * @return 解析结果
	 */
	public Map<String, Object> parseExcelReturnMap(Workbook workbook) {
		List<String[][]> resultDataList = new ArrayList<>();
		Map<String, Object> map = new HashMap<>(16);
		List<String> sheetNameList = new ArrayList<>();
		// 解析sheet
		for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
			Sheet sheet = workbook.getSheetAt(sheetNum);
			// 校验sheet是否合法
			if (sheet == null) {
				continue;
			}
			sheetNameList.add(sheet.getSheetName());
			// 获取第一行数据
			//获得最大行数
			int rowEnd = sheet.getLastRowNum();
			int maxCellNum = 0;
			//获取最大列
			for (int rowNum = 0; rowNum < rowEnd; rowNum++) {
				Row row = sheet.getRow(rowNum);
				if (row == null) {
					continue;
				}
				int cellNum = 0;
				try {
					cellNum = row.getLastCellNum();
				} catch (Exception e) {
					logger.error("获取列数失败");
				}
				if (cellNum > maxCellNum) {
					maxCellNum = cellNum;
				}
			}
			String[][] arr = new String[rowEnd + 1][maxCellNum];
			
			for (int rowNum = 0; rowNum <= rowEnd; rowNum++) {
				Row row = sheet.getRow(rowNum);
				if (null == row) {
					continue;
				}
				arr = convertRowToData(row, arr, rowNum);
			}
			resultDataList.add(arr);
		}
		map.put("list", resultDataList);
		map.put("sheetNameList", sheetNameList);
		return map;
	}
	
	/**
	 * 将单元格内容转换为字符串
	 *
	 * @param cell
	 * @return
	 */
	public static String convertCellValueToString(Cell cell) {
		if (cell == null) {
			return null;
		}
		String returnValue = null;
		//数字
		if (cell.getCellTypeEnum() == CellType.NUMERIC) {
			Double doubleValue = cell.getNumericCellValue();
			// 格式化科学计数法，取一位整数
			DecimalFormat df = new DecimalFormat("0.00");
			returnValue = df.format(doubleValue);
		} else if (cell.getCellTypeEnum() == CellType.STRING) {
			//字符串
			returnValue = cell.getStringCellValue();
		} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
			//布尔
			Boolean booleanValue = cell.getBooleanCellValue();
			returnValue = booleanValue.toString();
		} else if (cell.getCellTypeEnum() == CellType.BLANK) {
			//空值
			
		} else if (cell.getCellTypeEnum() == CellType.FORMULA) {
			//公式
			try {
				returnValue = String.valueOf(cell.getStringCellValue());
			} catch (IllegalStateException e) {
				returnValue = String.valueOf(cell.getNumericCellValue());
			}
		} else if (cell.getCellTypeEnum() == CellType.ERROR) {
		
		}
		return returnValue;
	}
	
	/**
	 * @param row 行数据
	 * @return 解析
	 */
	public abstract String[][] convertRowToData(Row row, String[][] arr, int rowIndex);
	
}

